﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ReadWriteExcel;

namespace WinFormsExcel
{
    public partial class FrmExcel : Form
    {
        private string pointPath;
        private string linePath;
        public FrmExcel()
        {
            InitializeComponent();
        }

        private void FrmExcel_Load(object sender, EventArgs e)
        {



        }
        private void InitialDataGridView(DataGridView dataGridView1, string filepath)
        {
            DataTable pDataTable = new DataTable();
            pDataTable = ClsExcelUtility.ExcelToDataTable(filepath, true);

            BindingSource bs = new BindingSource();
            bs.DataSource = pDataTable;
            dataGridView1.DataSource = bs;
        }
        /// <summary>
        /// 打开点表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOpenPoint_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Multiselect = true;
            openFileDialog.Title = "请选择Excel文件";//对话框标题
            openFileDialog.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx";
            openFileDialog.RestoreDirectory = true;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                string file = openFileDialog.FileName.ToString();
                this.textBoxPoint.Text = file;
                pointPath = file;
                InitialDataGridView(dataGridViewPoint, file);
            }
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOutputExcelPoint_Click(object sender, EventArgs e)
        {
            DataTable pDataTable = new DataTable();
            pDataTable = ClsExcelUtility.ExcelToDataTable(pointPath, true);
            pDataTable = UpdatePointDataTable(pDataTable);
            ClsExcelUtility.DataTableToExcel(pDataTable, "转换点表");
        }
        /// <summary>
        /// 修改数据表DataTable某一列的类型和记录值(正确步骤：1.克隆表结构，2.修改列类型，3.修改记录值，4.返回希望的结果)
        /// </summary>
        /// <param name="argDataTable">数据表DataTable</param>
        /// <returns>数据表DataTable</returns>
        private DataTable UpdatePointDataTable(DataTable argDataTable)
        {
            DataTable dtResult = new DataTable();
            //克隆表结构
            dtResult = argDataTable.Clone();
            foreach (DataRow row in argDataTable.Rows)
            {
                DataRow rowNew = dtResult.NewRow();
                foreach (System.Data.DataColumn k in argDataTable.Columns)
                {
                    string columnName = k.ColumnName;
                    if (columnName == "X")
                    {
                        rowNew[columnName] = Convert.ToDouble(row[columnName]) + 100000;
                    }
                    else if (columnName == "Y")
                    {
                        rowNew[columnName] = Convert.ToDouble(row[columnName]) + 2000000;
                    }
                    else if (columnName == "PLPTNO")
                    {
                        if (checkBoxPointSuffix.Checked && textBoxPointSuffix.Text != null && textBoxPointSuffix.Text.ToString().Trim() != "")
                        {
                            rowNew[columnName] = row[columnName] + textBoxPointSuffix.Text.ToString().Trim();
                        }
                        else
                        {
                            rowNew[columnName] = row[columnName];
                        }
                    }
                    else
                    {
                        rowNew[columnName] = row[columnName];
                    }
                }
                dtResult.Rows.Add(rowNew);
            }
            //返回希望的结果
            return dtResult;
        }
        /// <summary>
        /// 修改数据表DataTable某一列的类型和记录值(正确步骤：1.克隆表结构，2.修改列类型，3.修改记录值，4.返回希望的结果)
        /// </summary>
        /// <param name="argDataTable">数据表DataTable</param>
        /// <returns>数据表DataTable</returns>
        private DataTable UpdateLineDataTable(DataTable argDataTable)
        {
            DataTable dtResult = new DataTable();
            //克隆表结构
            dtResult = argDataTable.Clone();
            bool existPLID = false;
            foreach (DataColumn column in dtResult.Columns)
            {
                string columnName = column.ColumnName;
                if (columnName == "PLID")
                {
                    existPLID = true;
                    break;
                }
            }
            //不存在PLID字段，就新增一列
            if (!existPLID)
            {
                DataColumn dataColumn = new DataColumn("PLID");
                dtResult.Columns.Add(dataColumn);
            }

            foreach (DataRow row in argDataTable.Rows)
            {
                DataRow rowNew = dtResult.NewRow();

                foreach (System.Data.DataColumn k in argDataTable.Columns)
                {
                    string columnName = k.ColumnName;
                    rowNew[columnName] = row[columnName];
                    rowNew["PLID"] = row["PLPT0"].ToString().Trim() + row["PLPT1"].ToString().Trim();

                }
                dtResult.Rows.Add(rowNew);
            }
            //返回希望的结果
            return dtResult;
        }
        /// <summary>
        /// 修改数据表DataTable某一列的类型和记录值(正确步骤：1.克隆表结构，2.修改列类型，3.修改记录值，4.返回希望的结果)
        /// </summary>
        /// <param name="argDataTable">数据表DataTable</param>
        /// <returns>数据表DataTable</returns>
        private DataTable SuffixLineDataTable(DataTable argDataTable)
        {
            DataTable dtResult = new DataTable();
            //克隆表结构
            dtResult = argDataTable.Clone();
            foreach (DataRow row in argDataTable.Rows)
            {
                DataRow rowNew = dtResult.NewRow();

                foreach (System.Data.DataColumn k in argDataTable.Columns)
                {
                    string columnName = k.ColumnName;
                    if (columnName == "PLPT0" || columnName == "PLPT1")
                    {
                        if (checkBoxLineSuffix.Checked && this.textBoxLineSuffix.Text != null && this.textBoxLineSuffix.Text.ToString().Trim() != "")
                        {
                            rowNew[columnName] = row[columnName] + this.textBoxLineSuffix.Text.ToString().Trim();
                        }
                    }
                    else
                    {
                        rowNew[columnName] = row[columnName];
                    }

                }
                dtResult.Rows.Add(rowNew);
            }
            //返回希望的结果
            return dtResult;
        }
        /// <summary>
        /// 打开线表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOpenLine_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Multiselect = true;
            openFileDialog.Title = "请选择Excel文件";//对话框标题
            openFileDialog.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx";
            openFileDialog.RestoreDirectory = true;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                string file = openFileDialog.FileName.ToString();
                this.textBoxLine.Text = file;
                linePath = file;
                InitialDataGridView(dataGridViewLine, file);
            }
        }
        /// <summary>
        /// 导出线Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOutputExcelLine_Click(object sender, EventArgs e)
        {
            DataTable pDataTable = new DataTable();
            pDataTable = ClsExcelUtility.ExcelToDataTable(linePath, true);
            if (checkBoxLineSuffix.Checked)
            {
            	pDataTable = SuffixLineDataTable(pDataTable);
            }
            pDataTable = UpdateLineDataTable(pDataTable);
            ClsExcelUtility.DataTableToExcel(pDataTable, "转换线表");
        }

    }
}
